/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Product;
import model.Receipt;
import model.Receiptitem;
import model.Request;
import model.Staff;

/**
 *
 * @author Trang
 */
public class RequestDAO {
     private Connection con;
    public RequestDAO() {
        con = DBFactory.createConnection("onlineshopping");
    }
    public boolean addReceip(Request request) {

        boolean result = false;
//        List<Receiptitem> receiptitems = new ArrayList<Receiptitem>();

        try {

            String sqlst = "INSERT INTO request(Requester_idStaff, Approver_idStaff, reason, status, createDate, Product_idProduct,quantity) "
                    + "VALUES (?, ?, ?, ?, ?, ?, ?)";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            //receiptitems = receipt.getReceiptitemList();        

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, request.getStaff1().getIdStaff());
            ps.setInt(2, request.getStaff2().getIdStaff());
            ps.setString(3, request.getReason());
            ps.setString(4, "Unprocess");
            ps.setDate(5, new java.sql.Date (current.getTime()));
            ps.setInt(6, request.getProduct().getIdProduct());
            ps.setInt(7, request.getQuantity());  
            ps.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(RequestDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        //System.out.println("result" + result);
        return result;
    }
    
    public boolean addDraffReceip(Request request) {

        boolean result = false;
//        List<Receiptitem> receiptitems = new ArrayList<Receiptitem>();

        try {

            String sqlst = "INSERT INTO request(Requester_idStaff, reason, status, createDate, Product_idProduct, quantity) "
                    + "VALUES (?, ?, ?, ?, ?,?)";
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            Date current = new Date();
            //receiptitems = receipt.getReceiptitemList();        

            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, request.getStaff1().getIdStaff());
            ps.setString(2, request.getReason());
            ps.setString(3, "Draff");
            ps.setDate(4, new java.sql.Date (current.getTime()));
            ps.setInt(5, request.getProduct().getIdProduct());
            ps.setInt(6,request.getQuantity());
            ps.executeUpdate();

            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(RequestDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        //System.out.println("result" + result);
        return result;
    }
    
    public Request getRequest() {
        Request result = new Request();

        try {
            String sqlst = "SELECT * FROM request LIMIT 1";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
    
                  Product product = (new ProductDAO().getProductShortFromProductId(Product_idProduct));
                  Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  result = new Request(idRequest, staff, reason, product, quantiy);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    public List<Request> getUnprocessRequest(Staff Approver) {
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request Where request.status = ? and Approver_idStaff= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Unprocess");
            ps.setInt(2, Approver.getIdStaff());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
                  Date createDate = (Date) rs.getDate("createDate"); 
    
                  Product product = (new ProductDAO().getProductShortFromProductId(Product_idProduct));
                  Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  Request newrequest = new Request(idRequest, staff, reason, product, quantiy, createDate);
                  result.add(newrequest);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
    
   
    public int getUnprocessRequestNumber(Staff Approver) {
        int resultreturn = 0;
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request Where request.status = ? and Approver_idStaff= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Unprocess");
            ps.setInt(2, Approver.getIdStaff());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
                  Date createDate = (Date) rs.getDate("createDate"); 
    
                  Product product = (new ProductDAO().getProductShortFromProductId(Product_idProduct));
                  Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  Request newrequest = new Request(idRequest, staff, reason, product, quantiy, createDate);
                  result.add(newrequest);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        resultreturn = result.size();
        return resultreturn;
    }
    
    public int getProcessingRequestNumber(Staff Enforcement) {
        int resultreturn = 0;
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request Where request.status = ? and Enforcement_idStaff= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Processing");
            ps.setInt(2, Enforcement.getIdStaff());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                //resultreturn++;
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
                  Date createDate = (Date) rs.getDate("createDate"); 
    
                  Product product = (new ProductDAO().getProductShortFromProductId(Product_idProduct));
                  Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  Request newrequest = new Request(idRequest, staff, reason, product, quantiy, createDate);
                  result.add(newrequest);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        resultreturn = result.size();
        return resultreturn;
    }
    
    public Request getRequestByRequestId(Integer rid) {
        Request result = new Request();

        try {
            String sqlst = "SELECT * FROM request WHERE idRequest = ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setInt(1, rid);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
                  Date createDate = (Date) rs.getDate("createDate"); 
    
                  Product product = (new ProductDAO().getProductShortFromProductId(Product_idProduct));
                  Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  result = new Request(idRequest, staff, reason, product, quantiy, createDate);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     public boolean updateRequest(Integer idRequest, Integer idStaff, String approve){
        boolean result = false;
        
        try {
            String sqlst = "UPDATE request "
                    + "SET Enforcement_idStaff = ?, "
                    + "status = ?, "
                    + "approve = ? "
                    + "WHERE idRequest = ?";
            
            PreparedStatement ps = con.prepareStatement(sqlst);
            
            ps.setInt(1, idStaff);
            ps.setString(2, "Processing");
            ps.setString(3, approve);
            ps.setInt(4, idRequest);
            //System.out.println(sqlst);
            ps.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(AuthenticationDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return result;
    }
     
      public List<Request> getAllRequest() {
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                  Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  String approve = rs.getString("approve");
                  String status = rs.getString("status");
                  Date createDate = (Date) rs.getDate("createDate"); 
                  
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
    
//                  Product product = (new ProductDAO().getProductFromProductId(Product_idProduct));
                    Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  
                  Request result1 = new Request(idRequest, staff, approve, status, createDate);
                          //(idRequest, staff, reason, product, quantiy);
                  result.add(result1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }

        return result;
    }
     public List<Request> getDirectorReceiveRequest(Staff Approver) {
        int resultreturn = 0;
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request Where request.status != ? and Approver_idStaff= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Draff");
            ps.setInt(2, Approver.getIdStaff());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                 Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  String approve = rs.getString("approve");
                  String status = rs.getString("status");
                  Date createDate = (Date) rs.getDate("createDate"); 
                  
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
    
//                  Product product = (new ProductDAO().getProductFromProductId(Product_idProduct));
                    Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  
                  Request result1 = new Request(idRequest, staff, approve, status, createDate);
                          //(idRequest, staff, reason, product, quantiy);
                  result.add(result1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        //resultreturn = result.size();
        return result;
    } 
     public List<Request> getDraffRequest(Staff requester) {
        int resultreturn = 0;
        List<Request> result = new ArrayList<Request>();

        try {
            String sqlst = "SELECT * FROM request Where request.status = ? and Requester_idStaff= ?";
            PreparedStatement ps = con.prepareStatement(sqlst);
            ps.setString(1, "Draff");
            ps.setInt(2, requester.getIdStaff());
            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                 Integer idRequest = rs.getInt("idRequest");
                  Integer Requester_idStaff = rs.getInt("Requester_idStaff");
                  String reason = rs.getString("reason");
                  String approve = rs.getString("approve");
                  String status = rs.getString("status");
                  Date createDate = (Date) rs.getDate("createDate"); 
                  
                  Integer Product_idProduct = rs.getInt("Product_idProduct");
                  int quantiy = rs.getInt("quantity");
    
//                  Product product = (new ProductDAO().getProductFromProductId(Product_idProduct));
                    Staff staff = (new StaffDAO().getStaffByStaffId(Requester_idStaff));
                  
                  Request result1 = new Request(idRequest, staff, approve, status, createDate);
                          //(idRequest, staff, reason, product, quantiy);
                  result.add(result1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        //resultreturn = result.size();
        return result;
    } 
    
}
